﻿namespace TendEase.DataAccess
{
    using System;
    using System.Data;
    using System.Data.Common;
    using System.Collections.Generic;
    using System.Linq;
    using TendEase.DataAccess.Internal;

    #region DataAccessFactory

    public class DataAccessConfig
    {
        public IDataProvider Provider { get; set; }
        public string ConnectionString { get; set; }
    }

    public class DataAccessFactory
    {
        #region Create By DataAccessConfig
   
        /// <summary>
        /// 获取手动管理DataAccess对象, 调用查询函数之前需要手动打开数据库, 通过下列三个函数
        /// BeginTransaction, CommitTransaction, RollbackTransaction 手动处理事务
        /// </summary>
        /// <param name="DbProviderFactory">数据库提供程序工厂</param>
        /// <param name="connectionString">连接串</param>
        /// <returns>DataAccess对象</returns>
        public static IDataAccess New(DataAccessConfig config)
        {
            return new ManualDataAccess(config.Provider.Factory, config.ConnectionString);
        }
        #endregion

        #region Create By DbProviderFactory

        
        /// <summary>
        /// 获取手动管理DataAccess对象, 调用查询函数之前需要手动打开数据库, 通过下列三个函数
        /// BeginTransaction, CommitTransaction, RollbackTransaction 手动处理事务
        /// </summary>
        /// <param name="DbProviderFactory">数据库提供程序工厂</param>
        /// <param name="connectionString">连接串</param>
        /// <returns>DataAccess对象</returns>
        public static IDataAccess New(DbProviderFactory factory, string connectionString)
        {
            return new ManualDataAccess(factory, connectionString);
        }

        #endregion Create By DbProviderFactory

        #region Create By Default DbProviderFactory

        public static DbProviderFactory DefaultDbProvider { get; set; }

        private static readonly string DbProviderNullMessage = "set <DefaultDbProvider> before user this overload";

        /// <summary>
        /// 获取手动管理DataAccess对象, 调用查询函数之前需要手动打开数据库, 通过下列三个函数
        /// BeginTransaction, CommitTransaction, RollbackTransaction 手动处理事务
        /// </summary>
        /// <param name="connectionString">连接串</param>
        /// <returns>DataAccess对象</returns>
        public static IDataAccess New(string connectionString)
        {
            if (DefaultDbProvider == null)
                throw new NullReferenceException(DbProviderNullMessage);

            return new ManualDataAccess(DefaultDbProvider, connectionString);
        }

        #endregion Create By Default DbProviderFactory

        #region ClearPool Implement

        public static Action ClearPoolImpl { get; set; }

        #endregion ClearPool Implement
    }

    #endregion DataAccessFactory

    #region IDataAccess

    public interface ITransaction : IDisposable
    {
        void Commit();

        void Rollback();
    }

    public interface IDataAccess : IDisposable
    {
        void Open();
        void Close();

        ITransaction CreateTransaction();

        void BeginTransaction();

        void CommitTransaction();

        void RollbackTransaction();

        IDbDataParameter CreateParameter();

        IDbDataParameter CreateParameter(string name, object value);

        IDbDataParameter CreateParameter(string name, ParameterDirection pd);

        IDbDataParameter CreateParameter(string name, object value, ParameterDirection pd);

        IDbDataParameter CreateParameter(string name, DbType type, object value);

        IDbDataParameter CreateParameter(string name, DbType type, ParameterDirection pd);

        IDbDataParameter CreateParameter(string name, DbType type, object value, ParameterDirection pd);

        IDbDataParameter[] CreateParameterArray(uint count);

        int ExecuteNonQuery(string sql);

        int ExecuteNonQueryWithParams(string sql, params IDbDataParameter[] parameters);

        int ExecuteStoredProcedureNonQuery(string storedProcedureName, params IDbDataParameter[] parameters);

        int ExecuteStoredProcedureTextNonQuery(string text, params IDbDataParameter[] parameters);

        DataTable Execute(string sql);

        DataTable ExecuteWithParams(string sql, params IDbDataParameter[] parameters);

        DataTable ExecuteStoredProcedure(string storedProcedureName, params IDbDataParameter[] parameters);

        DataTable ExecuteStoredProcedureText(string text, params IDbDataParameter[] parameters);

        object ExecuteFunction(string funcName, params IDbDataParameter[] parameters);

        object ExecuteFunctionText(string text, params IDbDataParameter[] parameters);

        void Fill(string sql, DataTable table);

        void UpdateTable(DataTable tb, string sql);

        void CloseReader(IDataReader reader);

        IDataReader ExecuteReader(string sql);

        IDataReader ExecuteReader(string sql, int timeout);

        IDataReader ExecuteReader(string sql, CommandBehavior behavior);

        IDataReader ExecuteReaderWithParams(string sql, CommandBehavior behavior, params IDbDataParameter[] parameters);

        IDataReader ExecuteReaderWithStoredProcedure(string storedProcedureName, CommandBehavior behavior, params IDbDataParameter[] parameters);

        IDataReader ExecuteReaderWithStoredProcedureText(string text, CommandBehavior behavior, params IDbDataParameter[] parameters);

        object ExecuteScalar(string sql);

        object ExecuteScalarWithParams(string sql, params IDbDataParameter[] parameters);
    }

    #endregion IDataAccess

    #region DataReaderExtensions

    public static class DataReaderExtensions
    {
        public static string GetStringEx(this IDataReader reader, object indexOrName, string valueWhenDBNull = default(string))
        {
            return Convert.ToString(reader.GetValueEx(indexOrName, valueWhenDBNull));
        }

        public static bool GetBooleanEx(this IDataReader reader, object indexOrName, bool valueWhenDBNull = default(bool))
        {
            return Convert.ToBoolean(reader.GetValueEx(indexOrName, valueWhenDBNull));
        }

        public static DateTime GetDateTimeEx(this IDataReader reader, object indexOrName, DateTime valueWhenDBNull = default(DateTime))
        {
            return GetValueInternal(reader, reader.GetDateTime, indexOrName, valueWhenDBNull);
        }

        public static Decimal GetDecimalEx(this IDataReader reader, object indexOrName, Decimal valueWhenDBNull = default(Decimal))
        {
            return Convert.ToDecimal(reader.GetValueEx(indexOrName, valueWhenDBNull));
        }

        public static byte GetByteEx(this IDataReader reader, object indexOrName, byte valueWhenDBNull = default(byte))
        {
            return Convert.ToByte(reader.GetValueEx(indexOrName, valueWhenDBNull));
        }

        public static float GetFloatEx(this IDataReader reader, object indexOrName, float valueWhenDBNull = default(float))
        {
            return Convert.ToSingle(reader.GetValueEx(indexOrName, valueWhenDBNull));
        }

        public static double GetDoubleEx(this IDataReader reader, object indexOrName, double valueWhenDBNull = default(double))
        {
            return Convert.ToDouble(reader.GetValueEx(indexOrName, valueWhenDBNull));
        }

        public static UInt16 GetUInt16Ex(this IDataReader reader, object indexOrName, int valueWhenDBNull = default(int))
        {
            return Convert.ToUInt16(reader.GetValueEx(indexOrName, valueWhenDBNull));
        }

        public static Int32 GetInt32Ex(this IDataReader reader, object indexOrName, int valueWhenDBNull = default(int))
        {
            return Convert.ToInt32(reader.GetValueEx(indexOrName, valueWhenDBNull));
        }

        public static UInt32 GetUInt32Ex(this IDataReader reader, object indexOrName, int valueWhenDBNull = default(int))
        {
            return Convert.ToUInt32(reader.GetValueEx(indexOrName, valueWhenDBNull));
        }

        public static Int64 GetInt64Ex(this IDataReader reader, object indexOrName, long valueWhenDBNull = default(long))
        {
            return Convert.ToInt64(reader.GetValueEx(indexOrName, valueWhenDBNull));
        }

        public static object GetValueEx(this IDataReader reader, object indexOrName, object valueWhenDBNull = default(object))
        {
            return GetValueInternal(reader, reader.GetValue, indexOrName, valueWhenDBNull);
        }

        public static byte[] GetBlobEx(this IDataReader reader, object indexOrName, byte[] valueWhenDBNull = default(byte[]))
        {
            int index = 0;

            if (indexOrName is string)
                index = reader.GetOrdinal(indexOrName.ToString());
            else if (indexOrName is int)
                index = (int)indexOrName;
            else
                throw new ArgumentException("参数必须为字符串或者整型", "indexOrName");

            if (reader.IsDBNull(index))
                return valueWhenDBNull;

            System.Collections.Generic.List<byte> list = new List<byte>();

            int bufferSize = 65535;
            byte[] outbyte = new byte[bufferSize];
            long retval;
            long startIndex = 0;

            retval = reader.GetBytes(index, startIndex, outbyte, 0, bufferSize);

            while (retval == bufferSize)
            {
                list.AddRange(outbyte);

                startIndex += bufferSize;
                retval = reader.GetBytes(index, startIndex, outbyte, 0, bufferSize);
            }

            list.AddRange(outbyte.Take((int)retval));

            return list.ToArray();
        }

        private static TResult GetValueInternal<TResult>(IDataReader reader, Func<int, TResult> function, object indexOrName, TResult valueWhenDBNull)
        {
            int index = 0;

            if (indexOrName is string)
                index = reader.GetOrdinal(indexOrName.ToString());
            else if (indexOrName is int)
                index = (int)indexOrName;
            else
                throw new ArgumentException("参数必须为字符串或者整型", "indexOrName");

            if (reader.IsDBNull(index))
                return valueWhenDBNull;

            return function(index);
        }
    }
    #endregion

    namespace Internal
    {
        #region TransactionImplement
        internal class Transaction : ITransaction
        {
            public DbTransaction Trans { get; set; }

            public Transaction(DbTransaction trans)
            {
                this.Trans = trans;
            }

            public bool Commited { get; set; } = false;

            public void Commit()
            {
                Commited = true;
                Trans.Commit();
            }

            public void Dispose()
            {
                if (!Commited)
                    Rollback();

                Trans.Dispose();
            }

            public void Rollback()
            {
                Trans.Rollback();
            }
        }
        #endregion

        #region DataAccessBase

        internal abstract class DataAccessBase : IDataAccess
        {
            protected readonly DbProviderFactory _factory;
            protected readonly string _connectionString;

            public DataAccessBase(DbProviderFactory factory, string connectionString)
            {
                _factory = factory;
                _connectionString = connectionString;
            }

            #region virtual and abstract method

            #region db's open and close
            public virtual void Open() { }

            public virtual void Close() { }
            protected void OpenInternal(DbConnection conn)
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();

                    //说明连接池中连接无效
                    if (conn.State != ConnectionState.Open)
                    {
                        if (DataAccessFactory.ClearPoolImpl != null)
                            DataAccessFactory.ClearPoolImpl();

                        conn.Open();
                    }
                }
            }

            protected void CloseInternal(DbConnection conn)
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            #endregion db's open and close

            #region transcation

            public abstract ITransaction CreateTransaction();

            public abstract void BeginTransaction();

            public abstract void CommitTransaction();

            public abstract void RollbackTransaction();

            #endregion transcation

            #region OnXX functions

            protected virtual void OnCommandCreated(DbCommand cmd)
            {
            }

            protected virtual void OnDisposing()
            {
            }

            #endregion OnXX functions

            protected abstract DbConnection GetConnection();

            protected virtual int ExecuteNonQueryInternal(DbCommand cmd)
            {
                return cmd.ExecuteNonQuery();
            }

            protected virtual object ExecuteScalarInternal(DbCommand cmd)
            {
                return cmd.ExecuteScalar();
            }

            protected virtual void ExecuteInternal(DbDataAdapter adapter, DataTable table)
            {
                adapter.Fill(table);
            }

            protected virtual void UpdateTableInternal(DbDataAdapter adapter, DataTable tb)
            {
                adapter.Update(tb);
            }

            #endregion virtual and abstract method

            #region factory method

            public IDbDataParameter CreateParameter()
            {
                return _factory.CreateParameter();
            }

            public IDbDataParameter CreateParameter(string name, object value)
            {
                var param = _factory.CreateParameter();
                param.ParameterName = name;
                param.Value = value;
                return param;
            }

            public IDbDataParameter CreateParameter(string name, ParameterDirection pd)
            {
                var param = _factory.CreateParameter();
                param.ParameterName = name;
                param.Direction = pd;
                return param;
            }

            public IDbDataParameter CreateParameter(string name, object value, ParameterDirection pd)
            {
                var param = _factory.CreateParameter();
                param.ParameterName = name;
                param.Value = value;
                param.Direction = pd;
                return param;
            }

            public IDbDataParameter CreateParameter(string name, DbType type, object value)
            {
                var param = _factory.CreateParameter();
                param.ParameterName = name;
                param.Value = value;
                param.DbType = type;
                return param;
            }

            public IDbDataParameter CreateParameter(string name, DbType type, ParameterDirection pd)
            {
                var param = _factory.CreateParameter();
                param.ParameterName = name;
                param.Direction = pd;
                param.DbType = type;
                return param;
            }

            public IDbDataParameter CreateParameter(string name, DbType type, object value, ParameterDirection pd)
            {
                var param = _factory.CreateParameter();
                param.ParameterName = name;
                param.Value = value;
                param.Direction = pd;
                param.DbType = type;
                return param;
            }

            public IDbDataParameter[] CreateParameterArray(uint count)
            {
                IDbDataParameter[] array = new DbParameter[count];
                for (int i = 0; i < count; i++)
                {
                    array[i] = _factory.CreateParameter();
                }
                return array;
            }

            /***********************私有函数************************/

            private DbCommand CreateCommand(string sql)
            {
                return CreateCommand(sql, CommandType.Text);
            }

            private DbCommand CreateCommand(string sql, CommandType type)
            {
                DbCommand cmd = _factory.CreateCommand();
                OnCommandCreated(cmd);
                cmd.Connection = GetConnection();
                cmd.CommandText = sql;
                cmd.CommandType = type;
                return cmd;
            }

            private DbDataAdapter CreateAdapter(DbCommand cmd)
            {
                DbDataAdapter adapter = _factory.CreateDataAdapter();
                adapter.SelectCommand = cmd;
                return adapter;
            }

            private void BuildAdapterCommand(ref DbDataAdapter adapter)
            {
                DbCommandBuilder builder = _factory.CreateCommandBuilder();
                builder.DataAdapter = adapter;
                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();
                adapter.UpdateCommand = builder.GetUpdateCommand();

                OnCommandCreated(adapter.InsertCommand);
                OnCommandCreated(adapter.DeleteCommand);
                OnCommandCreated(adapter.UpdateCommand);
            }

            #endregion factory method

            #region DataBase Query

            public int ExecuteNonQuery(string sql)
            {
                DbCommand cmd = CreateCommand(sql);
                return ExecuteNonQueryInternal(cmd);
            }

            public DataTable Execute(string sql)
            {
                DbDataAdapter adapter = CreateAdapter(CreateCommand(sql));
                DataTable dt = new DataTable();
                ExecuteInternal(adapter, dt);
                adapter.Dispose();
                return dt;
            }

            public void Fill(string sql, DataTable table)
            {
                DbDataAdapter adapter = CreateAdapter(CreateCommand(sql));
                ExecuteInternal(adapter, table);
                adapter.Dispose();
            }

            public void UpdateTable(DataTable tb, string sql)
            {
                DbDataAdapter adapter = CreateAdapter(CreateCommand(sql));
                BuildAdapterCommand(ref adapter);
                UpdateTableInternal(adapter, tb);
                adapter.Dispose();
            }

            public object ExecuteScalar(string sql)
            {
                DbCommand cmd = CreateCommand(sql);
                return ExecuteScalarInternal(cmd);
            }

            public IDataReader ExecuteReader(string sql)
            {
                DbCommand cmd = CreateCommand(sql);
                return cmd.ExecuteReader();
            }

            public IDataReader ExecuteReader(string sql, int timeout)
            {
                DbCommand cmd = CreateCommand(sql);
                cmd.CommandTimeout = timeout;
                return cmd.ExecuteReader();
            }

            public IDataReader ExecuteReader(string sql, CommandBehavior behavior)
            {
                DbCommand cmd = CreateCommand(sql);
                return cmd.ExecuteReader(behavior);
            }

            public IDataReader ExecuteReaderWithParams(string sql, CommandBehavior behavior, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(sql);
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteReader(behavior);
            }

            public IDataReader ExecuteReaderWithStoredProcedure(string storedProcedureName, CommandBehavior behavior, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(storedProcedureName, CommandType.StoredProcedure);
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteReader(behavior);
            }

            public IDataReader ExecuteReaderWithStoredProcedureText(string text, CommandBehavior behavior, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(text);
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteReader(behavior);
            }

            public void CloseReader(IDataReader reader)
            {
                reader.Close();
            }

            public object ExecuteScalarWithParams(string sql, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(sql);
                cmd.Parameters.AddRange(parameters);
                return ExecuteScalarInternal(cmd);
            }

            public DataTable ExecuteWithParams(string sql, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(sql);
                cmd.Parameters.AddRange(parameters);
                DbDataAdapter adapter = CreateAdapter(cmd);
                DataTable dt = new DataTable();
                ExecuteInternal(adapter, dt);
                adapter.Dispose();
                return dt;
            }

            public int ExecuteNonQueryWithParams(string sql, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(sql);
                cmd.Parameters.AddRange(parameters);
                return ExecuteNonQueryInternal(cmd);
            }

            public DataTable ExecuteStoredProcedure(string storedProcedureName, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(storedProcedureName, CommandType.StoredProcedure);
                cmd.Parameters.AddRange(parameters);
                DbDataAdapter adapter = _factory.CreateDataAdapter();
                adapter.SelectCommand = cmd;
                DataTable dt = new DataTable();
                ExecuteInternal(adapter, dt);
                adapter.Dispose();
                return dt;
            }

            public int ExecuteStoredProcedureNonQuery(string storedProcedureName, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(storedProcedureName, CommandType.StoredProcedure);
                cmd.Parameters.AddRange(parameters);
                return ExecuteNonQueryInternal(cmd);
            }

            public DataTable ExecuteStoredProcedureText(string text, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(text);
                cmd.Parameters.AddRange(parameters);
                DbDataAdapter adapter = CreateAdapter(cmd);
                DataTable dt = new DataTable();
                ExecuteInternal(adapter, dt);
                adapter.Dispose();
                return dt;
            }

            public int ExecuteStoredProcedureTextNonQuery(string text, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(text);
                cmd.Parameters.AddRange(parameters);
                return ExecuteNonQueryInternal(cmd);
            }

            public object ExecuteFunction(string funcName, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(funcName, CommandType.StoredProcedure);
                cmd.Parameters.AddRange(parameters);
                return ExecuteScalarInternal(cmd);
            }

            public object ExecuteFunctionText(string text, params IDbDataParameter[] parameters)
            {
                DbCommand cmd = CreateCommand(text);
                cmd.Parameters.AddRange(parameters);
                return ExecuteScalarInternal(cmd);
            }

            #endregion DataBase Query

            #region IDisposable 成员

            private bool disposed = false;

            public void Dispose()
            {
                Dispose(true);
                GC.SuppressFinalize(this);
            }

            private void Dispose(bool disposing)
            {
                if (!this.disposed)
                {
                    if (disposing)
                    {
                        OnDisposing();
                    }

                    // Call the appropriate methods to clean up
                    // unmanaged resources here.
                    // If disposing is false,
                    // only the following code is executed.

                    disposed = true;
                }
            }

            ~DataAccessBase()
            {

                // Do not re-create Dispose clean-up code here.
                // Calling Dispose(false) is optimal in terms of
                // readability and maintainability.
                Dispose(false);
            }

            #endregion IDisposable 成员
        }

        #endregion DataAccessBase

      

        #region DataAccessImplement

        //Transcation, NoCatch
        internal class ManualDataAccess : DataAccessBase
        {
            private DbConnection _conn;
            private Transaction _transcation;

            public ManualDataAccess(DbProviderFactory factory, string connectionString, bool bOpen = true)
                : base(factory, connectionString)
            {
                _conn = _factory.CreateConnection();
                _conn.ConnectionString = _connectionString;

                if (bOpen) Open();
            }

            #region Transaction

            public override void Open()
            {
                OpenInternal(_conn);
            }

            public override void Close()
            {
                CloseInternal(_conn);
            }

            public override ITransaction CreateTransaction()
            {
                return new Transaction(_conn.BeginTransaction());
            }

            public override void BeginTransaction()
            {
                _transcation = CreateTransaction() as Transaction;
            }

            public override void CommitTransaction()
            {
                _transcation?.Commit();

                _transcation = null;
            }

            public override void RollbackTransaction()
            {
                _transcation?.Rollback();
                _transcation = null;
            }

            protected override void OnCommandCreated(DbCommand cmd)
            {
                if (_transcation != null)
                {
                    cmd.Transaction = _transcation.Trans;
                }
            }

            #endregion Transaction

            protected override DbConnection GetConnection()
            {
                return _conn;
            }

            protected override void OnDisposing()
            {
                CloseInternal(_conn);
            }

           
        }

        #endregion DataAccessImplement
    }

    #region IDataProvider
    public interface IDataProvider
    {
        string DataBaseName { get; }

        DbProviderFactory Factory { get; }

        IDbDataParameter GetOutCursorParameter(string name);

        IDbDataParameter GetInArrayParameter(string name, DbType type, Array arr);

        string GetConnectionString(string host, string port, string serviceName, string user, string pass);

        string GetTimeStampString(DateTime dt);

        string GetDateTimeString(DateTime dt);

        string GetPagingSQL(string sql, int number, int page);

        void ClearAllPools();
    }

#if DA_ORACLE
    namespace Oracle
    {
        using global::Oracle.ManagedDataAccess.Client;

        public class DataProvider : IDataProvider
        {
            public static DataProvider Default { get; } = new DataProvider();

            private const string ConnectionStringFmt = "User Id={0}; Password={1}; Validate Connection=true; Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST={2})(PORT={3})))(CONNECT_DATA=(SERVICE_NAME={4})));";
            private const string DbName = "Oracle";

            public string DataBaseName
            {
                get { return DbName; }
            }

            public string GetConnectionString(string host, string port, string serviceName, string user, string pass)
            {
                return string.Format(ConnectionStringFmt, user, pass, host, port, serviceName);
            }

            public DbProviderFactory Factory
            {
                get { return OracleClientFactory.Instance; }
            }

            public IDbDataParameter GetOutCursorParameter(string name)
            {
                var param = (this.Factory.CreateParameter() as OracleParameter);
                param.ParameterName = name;
                param.Direction = ParameterDirection.Output;
                param.OracleDbType = OracleDbType.RefCursor;
                return param;
            }

            public IDbDataParameter GetInArrayParameter(string name, DbType type, Array arr)
            {
                var param = (this.Factory.CreateParameter() as OracleParameter);
                param.ParameterName = name;
                param.DbType = type;
                param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
                param.Value = arr;
                param.Size = arr.Length;
                return param;
            }

            public string GetTimeStampString(DateTime dt)
            {
                return string.Format("to_timestamp('{0:yyyy-MM-dd HH:mm:ss}','yyyy-mm-dd hh24:mi:ss')", dt);
            }

            public string GetDateTimeString(DateTime dt)
            {
                return string.Format("to_date('{0:yyyy-MM-dd HH:mm:ss}','yyyy-mm-dd hh24:mi:ss')", dt);
            }

            public void ClearAllPools()
            {
                OracleConnection.ClearAllPools();
            }

            public string GetPagingSQL(string sql, int number, int page)
            {
                sql = sql.TrimEnd(' ', ';');

                return string.Format(
    @"SELECT *
    FROM (SELECT ROW_.*, ROWNUM ROWNUM_
          FROM ({0}) ROW_
         WHERE ROWNUM <= {1})
 WHERE ROWNUM_ > {2}", sql, (page + 1) * number, page * number);

            }

        }
    }

#endif

#if DA_PGSQL
    namespace PgSQL
    {
        public class DataProvider : IDataProvider
        {
            public static DataProvider Default { get; } = new DataProvider();

            private const string ConnectionStringFmt = "Server={2};Port={3};DATABASE={4};User id={0};password={1};SyncNotification=false;";
            private const string DbName = "PostgreSQL";

            public string DataBaseName {
                get { return DbName; }
            }

            public DbProviderFactory Provider {
                get { return Npgsql.NpgsqlFactory.Instance; }
            }

            public string GetConnectionString(string host, string port, string serviceName, string user, string pass) {
                return string.Format(ConnectionStringFmt, user, pass, host, port, serviceName);
            }

            public IDbDataParameter GetOutCursorParameter(string name) {
                var param = this.Provider.CreateParameter();
                param.ParameterName = name;
                param.Direction = ParameterDirection.Output;
                return param;
            }

            public IDbDataParameter GetInArrayParameter(string name, DbType type, Array arr) {
                throw new NotImplementedException();
            }

            public string GetTimeStampString(DateTime dt) {
                return string.Format("to_timestamp('{0:yyyy-MM-dd HH:mm:ss}','yyyy-mm-dd hh24:mi:ss')", dt);
            }

            public string GetDateTimeString(DateTime dt) {
                return string.Format("to_timestamp('{0:yyyy-MM-dd HH:mm:ss}','yyyy-mm-dd hh24:mi:ss')", dt);
            }

            public void ClearAllPools() {
                Npgsql.NpgsqlConnection.ClearAllPools();
            }


            public string GetPagingSQL(string sql, int number, int page) {
                sql = sql.TrimEnd(' ', ';');

                return string.Format("{0} limit {1} offset {2}", sql, number, page * number);
            }
        }
    }
    
#endif


    #endregion
}

